package service

import (
	"database/sql"
	"dsc-server/domain/dto"
	"dsc-server/pkg/model"
	"encoding/json"
	"fmt"
	"io/ioutil"
	"log"
	"net/http"
	"strconv"
	"strings"

	"github.com/gin-gonic/gin"
	"gorm.io/gorm"
)

// ApiService 处理API请求相关的服务
type ApiService struct {
	DB *gorm.DB
}

// NewApiService 创建ApiService实例
func NewApiService(db *gorm.DB) *ApiService {
	return &ApiService{DB: db}
}

// GetSqlParam 从请求中获取SQL参数
func (s *ApiService) GetSqlParam(c *gin.Context, config *model.ApiConfig) map[string]interface{} {
	params := make(map[string]interface{})

	// 获取请求参数
	requestParams := make(map[string][]string)

	// 处理URL查询参数
	for key, values := range c.Request.URL.Query() {
		requestParams[key] = values
	}

	// 处理表单参数
	if c.Request.Method == "POST" {
		if c.Request.Header.Get("Content-Type") == "application/x-www-form-urlencoded" {
			if err := c.Request.ParseForm(); err == nil {
				for key, values := range c.Request.PostForm {
					requestParams[key] = values
				}
			}
		}
	}

	// 处理JSON参数
	var jsonParams map[string]interface{}
	if c.Request.Method == "POST" && strings.Contains(c.Request.Header.Get("Content-Type"), "application/json") {
		body, err := ioutil.ReadAll(c.Request.Body)
		if err == nil && len(body) > 0 {
			if err := json.Unmarshal(body, &jsonParams); err == nil {
				for key, value := range jsonParams {
					// 处理数组类型
					if valueArr, ok := value.([]interface{}); ok {
						strArr := make([]string, len(valueArr))
						for i, v := range valueArr {
							strArr[i] = fmt.Sprintf("%v", v)
						}
						requestParams[key] = strArr
					} else {
						requestParams[key] = []string{fmt.Sprintf("%v", value)}
					}
				}
			}
		}
	}

	// 解析API配置中定义的参数
	var requestParamsDef []map[string]interface{}
	if err := json.Unmarshal([]byte(config.Params), &requestParamsDef); err != nil {
		log.Printf("解析API参数定义失败: %v", err)
		return params
	}

	// 根据参数定义处理请求参数
	for _, paramDef := range requestParamsDef {
		name := paramDef["name"].(string)
		paramType := paramDef["type"].(string)

		// 获取参数值
		values, exists := requestParams[name]
		if !exists || len(values) == 0 {
			params[name] = nil
			continue
		}

		// 处理数组类型参数
		if strings.HasPrefix(paramType, "Array") {
			if len(values) == 0 {
				params[name] = nil
				continue
			}

			switch paramType {
			case "Array<double>":
				var doubleValues []float64
				for _, v := range values {
					if val, err := strconv.ParseFloat(v, 64); err == nil {
						doubleValues = append(doubleValues, val)
					}
				}
				params[name] = doubleValues
			case "Array<bigint>":
				var longValues []int64
				for _, v := range values {
					if val, err := strconv.ParseInt(v, 10, 64); err == nil {
						longValues = append(longValues, val)
					}
				}
				params[name] = longValues
			case "Array<string>", "Array<date>":
				params[name] = values
			}
		} else {
			// 处理单值类型参数
			if len(values) == 0 || values[0] == "" {
				params[name] = nil
				continue
			}

			value := values[0]
			switch paramType {
			case "double":
				if val, err := strconv.ParseFloat(value, 64); err == nil {
					params[name] = val
				}
			case "bigint":
				if val, err := strconv.ParseInt(value, 10, 64); err == nil {
					params[name] = val
				}
			case "string", "date":
				params[name] = value
			}
		}
	}

	return params
}

// Execute 执行SQL
func (s *ApiService) Execute(isSelect int, datasource *model.Datasource, sqlParam map[string]interface{}, sqlTemplate string) dto.Result {
	return s.ExecuteSql(isSelect, datasource, sqlParam, sqlTemplate)
}

// ExecuteSql 执行SQL语句
func (s *ApiService) ExecuteSql(isSelect int, datasource *model.Datasource, sqlParam map[string]interface{}, sqlTemplate string) dto.Result {
	// 解析SQL模板，替换参数
	// 注意：这里简化了SQL解析逻辑，实际应用中需要更复杂的SQL解析器
	sql, jdbcParamValues := s.parseSql(sqlTemplate, sqlParam)

	// 记录SQL和参数
	log.Printf("SQL: %s, 参数: %v", sql, jdbcParamValues)

	// 记录参数字符串
	var sqlParamString strings.Builder
	for key, value := range sqlParam {
		sqlParamString.WriteString(key)
		sqlParamString.WriteString(":")
		sqlParamString.WriteString(fmt.Sprintf("%v", value))
		sqlParamString.WriteString(";")
	}
	log.Printf("参数字符串: %s", sqlParamString.String())

	// 获取数据库连接
	db, err := s.getConnection(datasource)
	if err != nil {
		return dto.FailureMessage(nil, fmt.Sprintf("获取数据库连接失败: %v", err))
	}
	defer db.Close()

	// 准备SQL语句
	stmt, err := db.Prepare(sql)
	if err != nil {
		return dto.FailureMessage(nil, fmt.Sprintf("准备SQL语句失败: %v", err))
	}
	defer stmt.Close()

	// 执行SQL
	switch isSelect {
	case 1: // 查询
		return s.executeQuery(stmt, jdbcParamValues)
	case 2: // 分页查询
		return s.executePageQuery(db, sql, sqlParam, jdbcParamValues)
	default: // 更新
		return s.executeUpdate(stmt, jdbcParamValues)
	}
}

// parseSql 解析SQL模板，替换参数
func (s *ApiService) parseSql(sqlTemplate string, params map[string]interface{}) (string, []interface{}) {
	// 简化版的SQL解析，实际应用中需要更复杂的SQL解析器
	// 这里假设SQL模板中使用 #{paramName} 形式的参数占位符

	var jdbcParamValues []interface{}

	// 替换参数
	for key, value := range params {
		placeholder := "#{" + key + "}"
		if strings.Contains(sqlTemplate, placeholder) {
			sqlTemplate = strings.Replace(sqlTemplate, placeholder, "?", -1)
			jdbcParamValues = append(jdbcParamValues, value)
		}
	}

	return sqlTemplate, jdbcParamValues
}

// getConnection 获取数据库连接
func (s *ApiService) getConnection(datasource *model.Datasource) (*sql.DB, error) {
	// 根据数据源类型构建连接字符串
	// 注意：实际应用中应该使用连接池管理数据库连接

	var db *sql.DB
	var err error

	switch datasource.Type {
	case "mysql":
		// 假设URL格式为: host:port/dbname
		db, err = sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s",
			datasource.Username, datasource.Password, datasource.Url, ""))
	case "postgres":
		// 假设URL已经是完整的连接字符串
		db, err = sql.Open("postgres", datasource.Url)
	default:
		return nil, fmt.Errorf("不支持的数据库类型: %s", datasource.Type)
	}

	return db, err
}

// executeQuery 执行查询SQL
func (s *ApiService) executeQuery(stmt *sql.Stmt, params []interface{}) dto.Result {
	// 执行查询
	rows, err := stmt.Query(params...)
	if err != nil {
		return dto.FailureMessage(nil, fmt.Sprintf("执行查询失败: %v", err))
	}
	defer rows.Close()

	// 获取列信息
	columns, err := rows.Columns()
	if err != nil {
		return dto.FailureMessage(nil, fmt.Sprintf("获取列信息失败: %v", err))
	}

	// 准备结果集
	var results []map[string]interface{}

	// 遍历结果集
	for rows.Next() {
		// 创建一个列的值的切片
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))

		// 初始化指针
		for i := range columns {
			valuePtrs[i] = &values[i]
		}

		// 扫描当前行
		if err := rows.Scan(valuePtrs...); err != nil {
			return dto.FailureMessage(nil, fmt.Sprintf("扫描行数据失败: %v", err))
		}

		// 创建行数据映射
		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]

			// 处理null值
			if val == nil {
				row[col] = ""
			} else {
				row[col] = val
			}
		}

		results = append(results, row)
	}

	return dto.Success(results)
}

// executePageQuery 执行分页查询
func (s *ApiService) executePageQuery(db *sql.DB, sql string, sqlParam map[string]interface{}, params []interface{}) dto.Result {
	// 获取分页参数
	pageSize, ok := sqlParam["pageSize"].(int64)
	if !ok {
		return dto.FailureMessage(nil, "分页参数pageSize无效")
	}

	currentPage, ok := sqlParam["currentPage"].(int64)
	if !ok {
		return dto.FailureMessage(nil, "分页参数currentPage无效")
	}

	// 构建计数SQL
	countSql := fmt.Sprintf("select count(*) from (%s) a", sql)

	// 执行计数查询
	var total int64
	row := db.QueryRow(countSql, params...)
	if err := row.Scan(&total); err != nil {
		return dto.FailureMessage(nil, fmt.Sprintf("查询总数失败: %v", err))
	}

	// 构建分页SQL
	offset := (currentPage - 1) * pageSize
	pageSql := fmt.Sprintf("%s limit %d offset %d", sql, pageSize, offset)

	// 执行分页查询
	rows, err := db.Query(pageSql, params...)
	if err != nil {
		return dto.FailureMessage(nil, fmt.Sprintf("执行分页查询失败: %v", err))
	}
	defer rows.Close()

	// 获取列信息
	columns, err := rows.Columns()
	if err != nil {
		return dto.FailureMessage(nil, fmt.Sprintf("获取列信息失败: %v", err))
	}

	// 准备结果集
	var records []map[string]interface{}

	// 遍历结果集
	for rows.Next() {
		// 创建一个列的值的切片
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))

		// 初始化指针
		for i := range columns {
			valuePtrs[i] = &values[i]
		}

		// 扫描当前行
		if err := rows.Scan(valuePtrs...); err != nil {
			return dto.FailureMessage(nil, fmt.Sprintf("扫描行数据失败: %v", err))
		}

		// 创建行数据映射
		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]

			// 处理null值
			if val == nil {
				row[col] = ""
			} else {
				row[col] = val
			}
		}

		records = append(records, row)
	}

	// 构建分页结果
	page := map[string]interface{}{
		"records":     records,
		"total":       total,
		"size":        pageSize,
		"current":     currentPage,
		"searchCount": true,
		"pages":       (total + pageSize - 1) / pageSize,
	}

	return dto.Success(page)
}

// executeUpdate 执行更新SQL
func (s *ApiService) executeUpdate(stmt *sql.Stmt, params []interface{}) dto.Result {
	// 执行更新
	result, err := stmt.Exec(params...)
	if err != nil {
		return dto.FailureMessage(nil, fmt.Sprintf("执行更新失败: %v", err))
	}

	// 获取影响行数
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return dto.FailureMessage(nil, fmt.Sprintf("获取影响行数失败: %v", err))
	}

	return dto.Success(fmt.Sprintf("sql修改数据行数: %d", rowsAffected))
}
